SELECT 
	s.Name SchemaName
	, so.name TableName
	, null ColumnName 
	, ep.Value [Description]
	, '' ColumnType
	, -1 ColumnID
	, so.name ObjectID
	, s.name SchemaID
FROM sys.objects so
INNER JOIN sys.schemas s ON s.[schema_id] = so.[schema_id]
LEFT JOIN sys.extended_properties ep ON ep.major_id = so.[object_id] AND ep.minor_id = 0
WHERE so.Type_Desc = 'USER_TABLE' AND so.Name <>'sysdiagrams'
UNION ALL 
SELECT 
	s.Name SchemaName
	, so.name TableName
	, c.name ColumnName 
	, ep.Value [Description]
	,upper(TYPE_NAME(c.system_type_id))
	+CASE WHEN TYPE_NAME(c.system_type_id) IN ('char','nchar','nvarchar','varchar') 
	THEN '(' + 
		CASE WHEN c.max_length = -1 
		THEN 'max'
		ELSE cast(c.max_length AS NVARCHAR(max))
		END 
	+ ')' else '' END  
	+ CASE WHEN c.is_nullable = 1 
	THEN ' null'
	ELSE ' not null'
	END
	ColumnType
	, c.column_id ColumnID
	, so.name ObjectID
	, s.name SchemaID
FROM sys.objects so
INNER JOIN sys.schemas s ON s.[schema_id] = so.[schema_id]
INNER JOIN sys.[columns] c ON c.[object_id] = so.[object_id]
LEFT JOIN sys.extended_properties ep ON ep.major_id = so.[object_id] AND c.column_id = ep.minor_id AND ep.Name = 'MS_Description'
WHERE so.Type_Desc = 'USER_TABLE' AND so.Name <>'sysdiagrams' 
ORDER BY SchemaID, ObjectID, ColumnID,  SchemaName, TableName, ColumnName